﻿using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Conventions;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using NPOI.HSSF.Record;
using NPOI.SS.UserModel;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using WcMatch.Attributes;
using WcMatch.Consts;
using WcMatch.Models;
using System.IO;
using System.Text.Json.Nodes;

namespace WcMatch.Controllers
{
    [Route("[controller]/[action]")]
    [ApiController]
    public class UniversityController : ControllerBase
    {
        UniversityContext _uniCxt;
        RecruitmentApplicationCxt _raCxt;
        CompanyContext _cCxt;
        JobContext _jobCxt;
        StuResumeContext _stuResumeContext;
        StudentContext _studentCxt;
        EthnicKindContext _ekCxt;
        UniversityDivisionContext _udCxt;
        DistrictContext _cityCxt;

        IWebHostEnvironment _webHostEnvironment;
        public UniversityController(UniversityContext uniCxt,
                                    RecruitmentApplicationCxt raCxt,
                                    CompanyContext cCxt,
                                    JobContext jobContext,
                                    StudentContext studentContext,
                                    StuResumeContext stuResumeContext,
                                    EthnicKindContext ekCxt,
                                    UniversityDivisionContext udCxt,
                                    DistrictContext cityCxt,
                                    IWebHostEnvironment webHost)
        {
            _uniCxt = uniCxt;
            _raCxt = raCxt;
            _cCxt = cCxt;
            _jobCxt = jobContext;
            _studentCxt = studentContext;
            _stuResumeContext = stuResumeContext;
            _ekCxt = ekCxt;
            _udCxt = udCxt;
            _cityCxt = cityCxt;
            _webHostEnvironment = webHost;
        }
        #region 用户管理
        [HttpPost]
        public async Task<IActionResult> AddRole(string uid, [FromForm] IFormCollection dict)
        {
            if (string.IsNullOrEmpty(uid))
                return BadRequest(new ActionStatus(-1, "参数异常"));

            University u = _uniCxt.Users.Where(s => s.Uid == uid).FirstOrDefault();
            if (u == null)
                return BadRequest(new ActionStatus(-1, "操作员账号不存在"));
            if (u.Role != 1)
                return Unauthorized(new ActionStatus(-1, "操作员权限不足"));

            University uni = new University();
            if (!ParseUserForm(ref uni, dict))
                return BadRequest(new ActionStatus(-1, "参数异常"));

            u = _uniCxt.Users.Where(s => s.UName == uni.UName).FirstOrDefault();
            if (u != null)
                return BadRequest(new ActionStatus(-1, "账号不可用，请更换后重试"));

            _uniCxt.Add(uni);
            int line = await _uniCxt.SaveChangesAsync();
            if (line > 0)
                return Ok(new ActionStatus(200, "新增成功", uni));
            else
                return BadRequest(new ActionStatus(-1, "内部错误，新增失败"));

        }

        private bool ParseUserForm(ref University uni, IFormCollection dict)
        {
            if (dict.Keys.Count == 0)
                return false;
            PropertyInfo[] pis = uni.GetType().GetProperties();
            foreach (var pi in pis)
            {
                string name = pi.Name.ToLower();
                if (dict.ContainsKey(name) && dict[name].Count() != 0)
                {
                    var value = dict[name][0];
                    if (pi.PropertyType.FullName.Contains("DateTime"))
                    {
                        DateTime dt;
                        if (!DateTime.TryParse(value, out dt))
                            return false;
                        pi.SetValue(uni, dt);
                    }
                    else if (pi.PropertyType.FullName.Contains("Int32"))
                    {
                        int v;
                        if (!int.TryParse(value, out v))
                            return false;
                        pi.SetValue(uni, v);
                    }
                    else if (pi.PropertyType.FullName.Contains("String"))
                    {
                        pi.SetValue(uni, value);
                    }
                }
            }
            return true;
        }

        [HttpPost]
        public async Task<IActionResult> Login(string uname, string pwd)
        {
            if (string.IsNullOrEmpty(uname) || string.IsNullOrEmpty(pwd))
                return BadRequest(new ActionStatus(-1, "参数异常"));

            var user = _uniCxt.Users.Where(s => s.UName == uname && s.Pwd == pwd).FirstOrDefault();
            if (user == null)
                return NotFound(new ActionStatus(-1, "账号或密码错误"));
            return Ok(new ActionStatus(200, "登录成功", user.Uid));
        }

        [HttpGet]
        public async Task<IActionResult> Get(string uid)
        {
            if (string.IsNullOrEmpty(uid))
                return BadRequest(new ActionStatus(-1, "参数异常"));
            var user = _uniCxt.Users.Where(s => s.Uid == uid).FirstOrDefault();
            if (user == null)
                return NotFound(new ActionStatus(-1, "用户不存在"));
            return Ok(new ActionStatus(200, "获取成功", user));
        }
        #endregion

        #region 招聘申请
        [HttpGet]
        public async Task<IActionResult> GetRAs(string uid)
        {
            //学校管理员首先获取 申请招聘的请求列表
            //管理员 拿到信息列表后，界面要显示查看详情按钮，逐个查看申请信息，并处理请求

            if (string.IsNullOrEmpty(uid))
                return BadRequest(new ActionStatus(-1, "参数异常"));
            var user = _uniCxt.Users.Where(s => s.Uid == uid).FirstOrDefault();
            if (user == null || user.Role > 2)
                return BadRequest(new ActionStatus(-1, "用户不存在或用户无权限处理申请"));

            var ras = _raCxt.RAs;
            return Ok(new ActionStatus(200, "查询成功", ras));
        }


        [HttpGet]
        public IActionResult LoadRaZZ(string cid)//企业的统一社会信用代码
        {
            if (string.IsNullOrEmpty(cid))
                return BadRequest(new ActionStatus(-1, "参数异常"));
            Company c = _cCxt.Companies.Where(c => c.CompanyId == cid).FirstOrDefault();
            if (c == null)
                return NotFound(new ActionStatus(-1, "未查询到企业"));
            else if (string.IsNullOrEmpty(c.ZzPath))
                return NotFound(new ActionStatus(-1, "营业执照材料不存在", string.Empty));


            if (!System.IO.File.Exists(c.ZzPath))
                return NotFound(new ActionStatus(-1, "内部异常", string.Empty));

            string ext = Path.GetExtension(c.ZzPath).ToLower();
            var contentTypeStr = GlobalDefines.ImgTypeDict[ext];
            using (var fs = new FileStream(c.ZzPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var bytes = new byte[fs.Length];
                fs.Read(bytes, 0, bytes.Length);
                fs.Close();
                return Ok(new ActionStatus(200, "获取成功", new FileContentResult(bytes, contentTypeStr)));
            }
        }

        [HttpGet]
        public IActionResult LoadRaIdCard(string cid)//企业的统一社会信用代码
        {
            if (string.IsNullOrEmpty(cid))
                return BadRequest(new ActionStatus(-1, "参数异常"));
            Company c = _cCxt.Companies.Where(c => c.CompanyId == cid).FirstOrDefault();
            if (c == null)
                return NotFound(new ActionStatus(-1, "未查询到企业"));
            else if (string.IsNullOrEmpty(c.IdCardPath))
                return NotFound(new ActionStatus(-1, "营业执照材料不存在", string.Empty));


            if (!System.IO.File.Exists(c.IdCardPath))
                return NotFound(new ActionStatus(-1, "内部异常", string.Empty));

            string ext = Path.GetExtension(c.IdCardPath).ToLower();
            var contentTypeStr = GlobalDefines.ImgTypeDict[ext];
            using (var fs = new FileStream(c.IdCardPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var bytes = new byte[fs.Length];
                fs.Read(bytes, 0, bytes.Length);
                fs.Close();
                return Ok(new ActionStatus(200, "获取成功", new FileContentResult(bytes, contentTypeStr)));
            }
        }

        [HttpGet]
        public IActionResult LoadRaSq(string cid)//企业的统一社会信用代码
        {
            if (string.IsNullOrEmpty(cid))
                return BadRequest(new ActionStatus(-1, "参数异常"));
            Company c = _cCxt.Companies.Where(c => c.CompanyId == cid).FirstOrDefault();
            if (c == null)
                return NotFound(new ActionStatus(-1, "未查询到企业"));
            else if (string.IsNullOrEmpty(c.SqPath))
                return NotFound(new ActionStatus(-1, "营业执照材料不存在", string.Empty));


            if (!System.IO.File.Exists(c.SqPath))
                return NotFound(new ActionStatus(-1, "内部异常", string.Empty));

            string ext = Path.GetExtension(c.SqPath).ToLower();
            var contentTypeStr = GlobalDefines.ImgTypeDict[ext];
            using (var fs = new FileStream(c.SqPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var bytes = new byte[fs.Length];
                fs.Read(bytes, 0, bytes.Length);
                fs.Close();
                return Ok(new ActionStatus(200, "获取成功", new FileContentResult(bytes, contentTypeStr)));
            }
        }

        [HttpPost]
        public IActionResult ProcessRa(string uid, int pass)
        {
            if (string.IsNullOrEmpty(uid)) return BadRequest(new ActionStatus(-1, "参数异常"));
            var ra = _raCxt.RAs.Where(s => s.Uid == uid).FirstOrDefault();
            if (ra == null) return NotFound(new ActionStatus(-1, "查询失败"));
            else
            {
                _raCxt.RAs.Remove(ra);
                if (_raCxt.SaveChanges() > 0)
                {
                    var c = _cCxt.Companies.Where(s => s.CompanyId == ra.ComId).FirstOrDefault();
                    c.PubJobDate = DateTime.Today;
                    c.CanPubJob = pass;
                    _cCxt.SaveChanges();
                    return Ok(new ActionStatus(200, "处理完成", ra.Uid));
                }
                else
                    return BadRequest(new ActionStatus(-1, "参数异常"));
            }
        }
        #endregion

        #region 获取企业、企业的招聘信息
        [HttpGet]
        public IActionResult AllJobs()
        {
            var lst = _jobCxt.Jobs.Where(s => s.ExpiryDate >= DateTime.Today);
            return Ok(new ActionStatus(200, "查找成功", lst));
        }

        [HttpPost]
        public IActionResult GetJobsBy([FromForm]IFormCollection dict)//string name, string college, string major, string degree, string comName, int mins
        {
            var lst = _jobCxt.Jobs.Where(s => s.ExpiryDate >= DateTime.Today).ToList();
            if(lst.Count() == 0)
                return Ok(new ActionStatus(200, "暂无有效岗位"));
            string name = string.Empty, college = string.Empty, major = string.Empty, degree = string.Empty, comName = string.Empty;
            int mins = -1;

            if (dict.ContainsKey("mins"))
                if (!int.TryParse(dict["mins"][0], out mins))
                    return BadRequest(new ActionStatus(-1, "参数异常"));

            if (dict.ContainsKey("name")) name = dict["name"][0];
            if (dict.ContainsKey("college")) college = dict["college"][0];
            if (dict.ContainsKey("major")) major = dict["major"][0];
            if (dict.ContainsKey("degree")) degree = dict["degree"][0];
            if (dict.ContainsKey("comName")) comName = dict["comName"][0];
            lst = FindJobBy(lst, name, college, major, degree, comName, mins);
            return Ok(new ActionStatus(200, "查找成功", lst));
        }

        private List<Job> FindJobBy(IEnumerable<Job> lst,  string name, string college, string major, string degree, string comName, int mins)
        {
            lst = lst.Where(s=>s.MaxWage >= mins);
            if (!string.IsNullOrEmpty(name)) lst = lst.Where(s => s.Name == name);
            if (!string.IsNullOrEmpty(college)) lst = lst.Where(s => s.CollegeRequirement == college);
            if (!string.IsNullOrEmpty(major)) lst = lst.Where(s => s.MajorRequirement == major);
            if (!string.IsNullOrEmpty(degree)) lst = lst.Where(s => s.DegreeRequirement == degree);
            if (!string.IsNullOrEmpty(comName)) lst = lst.Where(s => s.CompanyName == comName);
            return lst.ToList();
        }


        //[HttpGet]
        //public IActionResult GetJobsBy([FromForm] IFormCollection dict)
        //{
        //    var lst = _jobCxt.Jobs.Where(s => s.ExpiryDate >= DateTime.Today);
        //    if (dict.Keys.Count == 0)
        //        return Ok(new ActionStatus(200, "查找成功", lst));

        //    Job job = new Job();
        //    if (!ParseJobForm(dict, ref job))
        //        return BadRequest(new ActionStatus(-1, "参数异常"));

        //    var jobs = FindJob(lst.ToList(), job);
        //    return Ok(new ActionStatus(200, "查找成功", jobs));
        //}

        //private List<Job> FindJob(List<Job> lst, Job con)
        //{
        //    if (con.CollegeRequirement != null)
        //        lst = lst.Where(s => s.CollegeRequirement == con.CollegeRequirement).ToList();
        //    if (con.CompanyName != null)
        //        lst = lst.Where(s => s.CompanyName == con.CompanyName).ToList();
        //    if (con.DegreeRequirement != null)
        //        lst = lst.Where(s => s.DegreeRequirement == con.DegreeRequirement).ToList();
        //    if (con.MajorRequirement != null)
        //        lst = lst.Where(s => s.MajorRequirement == con.MajorRequirement).ToList();
        //    if (con.Name != null)
        //        lst = lst.Where(s => s.Name == con.Name).ToList();
        //    return lst;
        //}

        //private bool ParseJobForm(IFormCollection dict, ref Job job)
        //{
        //    if (dict.Keys.Count == 0)
        //        return false;
        //    PropertyInfo[] pis = job.GetType().GetProperties();
        //    foreach (PropertyInfo pi in pis)
        //    {
        //        string name = pi.Name.ToLower();
        //        if (!dict.ContainsKey(pi.Name))
        //            continue;
        //        var pValue = dict[name][0];
        //        if (string.IsNullOrEmpty(pValue))
        //            continue;
        //        bool serverUpdate = pi.IsDefined(typeof(OnlyServerUpdateAttribute));
        //        if (serverUpdate)
        //            continue;
        //        else if (pi.PropertyType.FullName.Contains("DateTime"))
        //        {
        //            DateTime dt;
        //            if (DateTime.TryParse(pValue, out dt))
        //                pi.SetValue(job, dt, null);
        //            else
        //                return false;
        //        }
        //        else if (pi.PropertyType.FullName.Contains("Int32"))
        //        {
        //            int v;
        //            if (Int32.TryParse(pValue, out v))
        //                pi.SetValue(job, v, null);
        //            else
        //                return false;
        //        }
        //        else if (pi.PropertyType.FullName.Contains("String"))
        //        {
        //            pi.SetValue(job, pValue, null);
        //        }
        //    }
        //    job.UpdateTime = DateTime.Today;
        //    return true;
        //}


        [HttpGet]
        public IActionResult GetJobsLike(string con)
        {
            if (string.IsNullOrEmpty(con))
                return BadRequest(new ActionStatus(-1, "参数异常"));
            var lst = _jobCxt.Jobs.Where(s => s.ExpiryDate >= DateTime.Today);
            lst = lst.Where(s => s.CollegeRequirement.Contains(con) ||
                           s.MajorRequirement.Contains(con) ||
                           s.DegreeRequirement.Contains(con) ||
                           s.CompanyName.Contains(con) ||
                           s.Name.Contains(con));

            return Ok(new ActionStatus(200, "查找成功", lst));

        }


        [HttpGet]
        public IActionResult AllComs()
        {
            var lst = _cCxt.Companies;
            return Ok(new ActionStatus(200, "查找成功", lst));
        }



        [HttpPost]
        public IActionResult GetComBy([FromForm] IFormCollection dict)
        {
            var lst = _cCxt.Companies.ToList();
            if (lst.Count() == 0)
                return Ok(new ActionStatus(200, "暂无注册企业"));

            string name = string.Empty,  cType = string.Empty, province = string.Empty, city = string.Empty, industry = string.Empty;

            if (dict.ContainsKey("name"))       name = dict["name"][0];
            if (dict.ContainsKey("cType"))      cType = dict["cType"][0];
            if (dict.ContainsKey("province"))   province = dict["province"][0];
            if (dict.ContainsKey("city"))       city = dict["city"][0];
            if (dict.ContainsKey("industry"))   industry = dict["industry"][0];
            lst = FindComBy(lst, name, cType, province, city, industry);
            return Ok(new ActionStatus(200, "查找成功", lst));
        }


        private List<Company> FindComBy(IEnumerable<Company>lst, string name, string cType, string province, string city, string industry)
        {
            if (!string.IsNullOrEmpty(name)) lst = lst.Where(s => s.Name == name);
            if (!string.IsNullOrEmpty(cType)) lst = lst.Where(s => s.CompanyType == cType);
            if (!string.IsNullOrEmpty(province)) lst = lst.Where(s => s.Province == province);
            if (!string.IsNullOrEmpty(city)) lst = lst.Where(s => s.City == city);
            if (!string.IsNullOrEmpty(industry)) lst = lst.Where(s => s.Industry == industry);
            return lst.ToList();
        }

        [HttpGet]
        public IActionResult GetComLike(string con)
        {
            if (string.IsNullOrEmpty(con)) return BadRequest(new ActionStatus( -1, "参数异常"));
            var lst = _cCxt.Companies.Where(s =>   s.Name.Contains(con) ||
                                                   s.CompanyType.Contains(con) ||
                                                   s.Province.Contains(con) ||
                                                   s.City.Contains(con) ||
                                                   s.Industry.Contains(con));

            return Ok(new ActionStatus(200, "查找成功", lst));
        }

        //private List<Company> ParseComQueryCondition(IFormCollection dict)
        //{
        //    StringBuilder sb = new StringBuilder("select * from t_user_company where ");
        //    var keys = dict.Keys.ToArray();
        //    int len = keys.Length;
        //    for (int i = 0; i < len; i++)
        //    {
        //        string key = keys[i];
        //        sb.Append($"{key} = '{dict[key][0]}'");
        //        if (i != len - 1)
        //            sb.Append(" and ");
        //        else
        //            sb.Append(";");
        //    }
        //    FormattableString sqlCmd = $"{sb.ToString()}";
        //    string propertyName = "Name";
        //    string propertyValue = "1234";
        //    return _cCxt.Companies.FromSqlRaw($"SELECT * FROM t_user_company WHERE {propertyName} = @{propertyValue}", propertyValue).ToList();
        //}
        #endregion


        #region 就业与职业发展
        [HttpGet]
        public IActionResult CareerDefault() 
        {
            var year = DateTime.Now.AddYears(-1);
            var resumeGroups = _stuResumeContext.Resumes.Where(s => s.UpdateTime > year).GroupBy(s=>s.CitizenId);

            List<StudentWithResume> srs = new List<StudentWithResume>();

            foreach (var item in resumeGroups)
            {
                string cid = item.Key;
                var student = _studentCxt.Students.Where(s => s.CitizenId == cid).FirstOrDefault();
                StudentWithResume sr = new StudentWithResume();
                sr.Brief = student;
                foreach (var resume in item)
                {
                    sr.Resumes.Add(resume);
                }
                srs.Add(sr);
            }
            return Ok(new ActionStatus(200,"查找成功", srs));
        }

        [HttpPost]
        public IActionResult SearchBy([FromForm]IFormCollection dict)
        {
            string name = null, college = null, major = null, cType = null, cName = null, startDt = null, endDt = null;
            DateTime dtStart, dtEnd;
            if (dict.Keys.Count == 0)
                return BadRequest(new ActionStatus(-1, "检索参数不可为空"));
            if (dict.ContainsKey("name"))              name = dict["name"];
            if (dict.ContainsKey("college"))        college = dict["college"];
            if (dict.ContainsKey("major"))            major = dict["major"];
            if (dict.ContainsKey("cType"))            cType = dict["cType"];
            if (dict.ContainsKey("cName"))            cName = dict["cName"];
            if (dict.ContainsKey("startDt"))        startDt = dict["startDt"];
            if (dict.ContainsKey("endDt"))            endDt = dict["endDt"];

            List<StuResume> lst = null;
            List<Student> lstStu = null;
            List<Company> lstCs = null;
            if (!string.IsNullOrEmpty(name))
            {
                lst = _stuResumeContext.Resumes.Where(s => s.Name == name).ToList();
                lstStu = _studentCxt.Students.Where(s=>s.Name == name).ToList();
                if (lst.Count == 0)
                    return Ok(new ActionStatus(200, "查询成功", lst));
            }
            if (!string.IsNullOrEmpty(college))
            {
                lstStu = _studentCxt.Students.Where(s => s.College == college).ToList();
                var cids = lstStu.Select(s => s.CitizenId).ToList();
                if (lst == null)
                    lst = _stuResumeContext.Resumes.Where(s => cids.Contains(s.CitizenId)).ToList();
                else
                    lst = lst.Where(s => cids.Contains(s.CitizenId)).ToList();
                if (lst.Count == 0)
                    return Ok(new ActionStatus(200, "查询成功", lst));
            }
            if (!string.IsNullOrEmpty(major))
            {
                if(lstStu == null)
                    lstStu = _studentCxt.Students.Where(s => s.Major == major).ToList();
                else
                    lstStu = lstStu.Where(s=>s.Major == major).ToList();

                var cids = lstStu.Select(s => s.CitizenId).ToList();
                if (lst == null)
                    lst = _stuResumeContext.Resumes.Where(s => cids.Contains(s.CitizenId)).ToList();
                else
                    lst = lst.Where(s => cids.Contains(s.CitizenId)).ToList();
                if (lst.Count == 0)
                    return Ok(new ActionStatus(200, "查询成功", lst));
            }
            if (!string.IsNullOrEmpty(cType))
            {

                lstCs = _cCxt.Companies.Where(s=>s.CompanyType == cType).ToList();
                var comIds = lstCs.Select(s=>s.CompanyId).ToList();
          
                if (lst == null)
                    lst = _stuResumeContext.Resumes.Where(s => comIds.Contains(s.CompanyId)).ToList();
                else
                    lst = lst.Where(s => comIds.Contains(s.CompanyId)).ToList();
                if (lst.Count == 0)
                    return Ok(new ActionStatus(200, "查询成功", lst));
            }
            if (!string.IsNullOrEmpty(cName))
            {
                lstCs = _cCxt.Companies.Where(s => s.Name == cName).ToList();
                var comIds = lstCs.Select(s => s.CompanyId).ToList();

                if (lst == null)
                    lst = _stuResumeContext.Resumes.Where(s => comIds.Contains(s.CompanyId)).ToList();
                else
                    lst = lst.Where(s => comIds.Contains(s.CompanyId)).ToList();
                if (lst.Count == 0)
                    return Ok(new ActionStatus(200, "查询成功", lst));
            }
            if (!string.IsNullOrEmpty(startDt))
            {
                if (!DateTime.TryParse(startDt, out dtStart))
                    return BadRequest(new ActionStatus(-1, "参数异常"));
                if (lstStu == null)
                    lstStu = _studentCxt.Students.Where(s => s.GraduationYear >= dtStart).ToList();
                else
                    lstStu = lstStu.Where(s => s.GraduationYear >= dtStart).ToList();
                var cids = lstStu.Select(s => s.CitizenId).ToList();
                if (lst == null)
                    lst = _stuResumeContext.Resumes.Where(s => cids.Contains(s.CitizenId)).ToList();
                else
                    lst = lst.Where(s => cids.Contains(s.CitizenId)).ToList();
                if (lst.Count == 0)
                    return Ok(new ActionStatus(200, "查询成功", lst));
            }
            if (!string.IsNullOrEmpty(endDt))
            {
                if (!DateTime.TryParse(endDt, out dtEnd))
                    return BadRequest(new ActionStatus(-1, "参数异常"));
                if (lstStu == null)
                    lstStu = _studentCxt.Students.Where(s => s.GraduationYear >= dtEnd).ToList();
                else
                    lstStu = lstStu.Where(s => s.GraduationYear >= dtEnd).ToList();
                var cids = lstStu.Select(s => s.CitizenId).ToList();
                if (lst == null)
                    lst = _stuResumeContext.Resumes.Where(s => cids.Contains(s.CitizenId)).ToList();
                else
                    lst = lst.Where(s => cids.Contains(s.CitizenId)).ToList();
                if (lst.Count == 0)
                    return Ok(new ActionStatus(200, "查询成功", lst));
            }
            if(lst == null)
                return BadRequest(new ActionStatus(-1, "检索参数不可为空"));
            var resumeGroups = lst.GroupBy(s => s.CitizenId);
            List<StudentWithResume> srs = new List<StudentWithResume>();
            foreach (var item in resumeGroups)
            {
                string cid = item.Key;
                var student = _studentCxt.Students.Where(s => s.CitizenId == cid).FirstOrDefault();
                StudentWithResume sr = new StudentWithResume();
                sr.Brief = student;
                foreach (var resume in item)
                {
                    sr.Resumes.Add(resume);
                }
                srs.Add(sr);
            }
            return Ok(new ActionStatus(200, "查找成功", srs));
        }

        [HttpPost]
        public async Task <IActionResult> ImportStudents([FromForm] IFormCollection dict)
        {
            //application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
            //application/vnd.ms-excel
            //text / csv
            //ContentType 
            IFormFile file = dict.Files["file"];
            if (file == null)
                return BadRequest(new ActionStatus(-1, "参数异常"));
            string fileExten = Path.GetExtension(file.FileName).ToLower();
            if (!GlobalDefines.ExcelTypeDict.Keys.Contains(fileExten))
                return BadRequest(new ActionStatus(-1, "文件格式不被支持"));

            IEnumerable<Student> lst = await ParseExcel(file);
            if (lst == null)
                return BadRequest(new ActionStatus(-1,"解析失败"));
            var cids = lst.Select(s => s.CitizenId).ToList();

            var cf = _studentCxt.Students.Where(s => cids.Contains(s.CitizenId)).ToArray();
            if(cf != null && cf.Count() > 0)
                _studentCxt.Students.RemoveRange(cf);
            _studentCxt.Students.AddRange(lst);
            int len = await _studentCxt.SaveChangesAsync();
            return Ok(new ActionStatus(200, $"导入成功{lst.Count()}条"));
        }

        private async Task<IEnumerable<Student>> ParseExcel(IFormFile file)
        {
            var dirPath = Path.Combine(_webHostEnvironment.ContentRootPath + GlobalDefines.Temp);
            if (!Directory.Exists(dirPath))
                Directory.CreateDirectory(dirPath);
            string filePath = Path.Combine(dirPath, Guid.NewGuid().ToString() + Path.GetExtension(file.FileName));

            using (var fs = System.IO.File.Create(filePath))
            {
                await file.CopyToAsync(fs);
                fs.Flush();
                fs.Close();
            }

            var uds = _udCxt.UniversityDivisions.ToList();
            var minzu = _ekCxt.EthnicKinds.ToList();
            var city = _cityCxt.Districts.ToList();

            List<Student> lst = new List<Student>();

            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {        
                IWorkbook wb = WorkbookFactory.Create(fs);
                var sheet = wb.GetSheetAt(0);
                if(sheet != null)
                {
                    int lastRowIx = sheet.LastRowNum + 1;
                    for (int i = 1; i < lastRowIx; i++)
                    {
                        var row = sheet.GetRow(i);
                        if (row == null)
                            break;
                        Student stu = new Student();
                        stu.Name            = row.GetCell(0).ToString();
                        
                        stu.Gender          = row.GetCell(1).ToString();
                        stu.StuId           = row.GetCell(2).ToString();
                        stu.UName = stu.StuId;
                        stu.HashedPwd = stu.StuId;


                        stu.CitizenId       = row.GetCell(3).ToString();

                        stu.EthnicGroups    = row.GetCell(4).ToString();
                        if (!string.IsNullOrEmpty(stu.EthnicGroups))
                        {
                            if (!stu.EthnicGroups.EndsWith("族")) stu.EthnicGroups += "族";
                            var t = minzu.Find(s => s.Name == stu.EthnicGroups);
                            if (t != null) stu.EthnicGroups = t.Id.ToString();
                            else return null;
                        }
                        else return null;

                        stu.College         = row.GetCell(5).ToString();
                        if (!string.IsNullOrEmpty(stu.College))
                        {
                            if (!stu.College.EndsWith("学院")) stu.College += "学院";
                            var t = uds.Find(s => s.Name == stu.College);
                            if (t != null) stu.College = t.Id.ToString();
                            else return null;                            
                        }
                        else return null;


                        stu.Major           = row.GetCell(6).ToString();
                        if (!string.IsNullOrEmpty(stu.Major))
                        {
                            var t = uds.Find(s => s.Name == stu.Major);
                            if (t != null) stu.Major = t.Id.ToString();
                            else return null;
                        }
                        else return null;

                        stu.Degree          = row.GetCell(7).ToString();

                        string eYear        = row.GetCell(8).ToString();
                        DateTime dt = DateTime.Now;
                        if (string.IsNullOrEmpty(eYear)) return null;
                        else if (DateTime.TryParse(eYear, out dt))
                            stu.EnrollmentYear = dt;
                        else return null;

                        eYear = row.GetCell(9).ToString();//stu.GraduationYear
                        if (string.IsNullOrEmpty(eYear)) return null;
                        else if (DateTime.TryParse(eYear, out dt))
                            stu.GraduationYear = dt;
                        else return null;

                        stu.Phone           = row.GetCell(10).ToString();

                        stu.ProvinceBorn    = row.GetCell(11).ToString();
                        if (!string.IsNullOrEmpty(stu.ProvinceBorn))
                        {
                            if (!stu.ProvinceBorn.EndsWith("省")) stu.ProvinceBorn += "省";
                            var t = city.Find(s => s.Name == stu.ProvinceBorn);
                            if (t != null) stu.ProvinceBorn = t.Id.ToString();
                            else return null;
                        }
                        else return null;


                        stu.CityBorn        = row.GetCell(12).ToString();
                        if (!string.IsNullOrEmpty(stu.CityBorn))
                        {
                            if (!stu.CityBorn.EndsWith("市")) stu.CityBorn += "市";
                            var t = city.Find(s => s.Name == stu.CityBorn);
                            if (t != null) stu.CityBorn = t.Id.ToString();
                            else return null;
                        }
                        else return null;

                        stu.FamilyAddr      = row.GetCell(13).ToString();
                        lst.Add(stu);
                    }
                }
                fs.Close();
            }

            lst = lst.DistinctBy(s => s.CitizenId).ToList();
            System.IO.File.Delete(filePath);

            return lst;
        }

        #endregion

        [HttpGet]
        public IActionResult GetStuInfoExcel()
        {
            var contentTypeStr = GlobalDefines.ExcelTypeDict[".xlsx"];
            var filePath = _webHostEnvironment.ContentRootPath + GlobalDefines.ModelFilePath+GlobalDefines.StuInfoFileName;
            using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                var bytes = new byte[fs.Length];
                fs.Read(bytes, 0, bytes.Length);
                fs.Close();
                //return Ok(new ActionStatus(200, "获取成功", new File());
                return File(bytes, contentTypeStr);
            }            
        }

        #region 数据分析
        [HttpGet]
        public IActionResult GenderStatistics()
        {
            int year = DateTime.Now.Year;
            var fiveYearAgo = DateTime.Parse($"{year-5}-1-1");
            var rs = _stuResumeContext.Resumes.Where(s=>s.BeginTime > fiveYearAgo).ToList().OrderByDescending(s => s.BeginTime).DistinctBy(s => s.CitizenId).ToList();//&& ((TimeSpan)(DateTime.Today - s.BeginTime)).TotalDays <= 365*5 
            var ids = rs.Select(s => s.CitizenId);

            var stus = _studentCxt.Students.Where(s => ids.Contains(s.CitizenId));
            int maleCnt = stus.Count(s => s.Gender == GlobalDefines.GenderMale);
            int femaleCnt = stus.Count(s => s.Gender == GlobalDefines.GenderFemale);
            JsonObject js = new JsonObject
            {
                ["maleCnt"] = maleCnt,
                ["femaleCnt"] = femaleCnt
            };
            return Ok(new ActionStatus(200, "查询成功", js));
        }


        [HttpGet]
        public IActionResult CollegeStatistics()
        {
            int year = DateTime.Now.Year;
            var fiveYearAgo = DateTime.Parse($"{year - 5}-1-1");
            var rs = _stuResumeContext.Resumes.Where(s => s.BeginTime > fiveYearAgo).ToList().OrderByDescending(s => s.BeginTime).DistinctBy(s => s.CitizenId).ToList();//&& ((TimeSpan)(DateTime.Today - s.BeginTime)).TotalDays <= 365*5             
            var ids = rs.Select(s => s.CitizenId);
            var stus = _studentCxt.Students.Where(s => ids.Contains(s.CitizenId));//近五年的学生

            var colleges = stus.Select(s => s.College).Where(s => s != null).Distinct().ToArray();
            JsonArray ja = new JsonArray();
            var uds = _udCxt.UniversityDivisions.ToList();

            for (int i = 0; i < colleges.Length; i++)
            {
                int cnt = stus.Count(s => s.College == colleges[i]);
                var ud = uds.Where(s => $"{s.Id}" == colleges[i]).FirstOrDefault();
                if (ud == null)
                    continue;
                var collegeName = ud.Name;
                JsonObject item = new JsonObject
                {
                    [$"{collegeName}"] = cnt,
                };
                ja.Add(item);
            }
            return Ok(new ActionStatus(200, "查询成功", ja));
        }


        [HttpGet]
        public IActionResult MajorStatistics()
        {
            var fiveYearAgo = DateTime.Now.AddYears(-5);
            var rs = _stuResumeContext.Resumes.Where(s =>s.BeginTime > fiveYearAgo).ToList().OrderByDescending(s => s.BeginTime).DistinctBy(s => s.CitizenId).ToList();//&& ((TimeSpan)(DateTime.Today - s.BeginTime)).TotalDays <= 365*5             
            var ids = rs.Select(s => s.CitizenId);
            var stus = _studentCxt.Students.Where(s => ids.Contains(s.CitizenId));//近五年的学生

            var majors = stus.Select(s => s.Major).Where(s => s != null).Distinct().ToArray();
            //Dictionary<string, int> majorCntDic = new Dictionary<string, int>();



            JsonArray ja = new JsonArray();
            var uds = _udCxt.UniversityDivisions.ToList();
            for (int i = 0; i < majors.Length; i++)
            {
                int cnt = stus.Count(s => s.Major == majors[i]);
                var ud = uds.Where(s => $"{s.Id}" == majors[i]).FirstOrDefault();
                if (ud == null)
                    continue;
                var majorName = ud.Name;
                JsonObject item = new JsonObject
                {
                    [$"{majorName}"] = cnt,
                };
                ja.Add(item);
            }
            return Ok(new ActionStatus(200, "查询成功", ja));
        }

        [HttpGet]
        public IActionResult CntYear()
        {
            //Dictionary<string, int> yearCnt = new Dictionary<string, int>();
            JsonArray ja = new JsonArray();
            int year = DateTime.Now.Year;
            var YearAgo1 = DateTime.Parse($"{year}-1-1");
            var YearAgo2 = DateTime.Parse($"{year - 1}-1-1");
            var YearAgo3 = DateTime.Parse($"{year - 2}-1-1");
            var YearAgo4 = DateTime.Parse($"{year - 3}-1-1");
            var YearAgo5 = DateTime.Parse($"{year - 4}-1-1");
            int one = _stuResumeContext.Resumes.Where(s =>  s.BeginTime >= YearAgo1).ToList().DistinctBy(s => s.CitizenId).Count();//&& ((TimeSpan)(DateTime.Today - s.BeginTime)).TotalDays <= 365*5             
            int two = _stuResumeContext.Resumes.Where(s =>   s.BeginTime >= YearAgo2 && s.BeginTime < YearAgo1).ToList().DistinctBy(s => s.CitizenId).Count();//&& ((TimeSpan)(DateTime.Today - s.BeginTime)).TotalDays <= 365*5             
            int three = _stuResumeContext.Resumes.Where(s =>   s.BeginTime >= YearAgo3 && s.BeginTime < YearAgo2).ToList().DistinctBy(s => s.CitizenId).Count();//&& ((TimeSpan)(DateTime.Today - s.BeginTime)).TotalDays <= 365*5             
            int four = _stuResumeContext.Resumes.Where(s => s.BeginTime >= YearAgo4 && s.BeginTime < YearAgo3).ToList().DistinctBy(s => s.CitizenId).Count();//&& ((TimeSpan)(DateTime.Today - s.BeginTime)).TotalDays <= 365*5             
            int five = _stuResumeContext.Resumes.Where(s =>  s.BeginTime >= YearAgo5 && s.BeginTime < YearAgo4).ToList().DistinctBy(s => s.CitizenId).Count();//&& ((TimeSpan)(DateTime.Today - s.BeginTime)).TotalDays <= 365*5             
            JsonObject ja1 = new JsonObject
            {
                [$"{DateTime.Now.Year}"] = one
            };
            JsonObject ja2 = new JsonObject
            {
                [$"{DateTime.Now.Year - 1}"] = two
            };
            JsonObject ja3 = new JsonObject
            {
                [$"{DateTime.Now.Year - 2}"] = three
            };
            JsonObject ja4 = new JsonObject
            {
                [$"{DateTime.Now.Year - 3}"] = four
            };
            JsonObject ja5 = new JsonObject
            {
                [$"{DateTime.Now.Year - 4}"] = five
            };

            return Ok(new ActionStatus(200, "查询成功", ja));
        }
        #endregion
    }
}
